#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Author:    御风
# @Mail:      zhong.yufeng@foxmail.com
# @Time:      2022/4/12 0:25


import pandas as pd
from sqlalchemy import Column, Integer, String, Date, DateTime
from utils.db.module import Base
from utils.db.database import DataBase


class AreaItem(Base):
    """
    CREATE TABLE `sys_deliver_area` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `area_name` varchar(255) DEFAULT NULL,
        `create_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
        `create_peo` varchar(255) DEFAULT NULL,
        `update_time` datetime DEFAULT NULL,
        `update_peo` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    """

    __tablename__ = "sys_deliver_area"

    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    area_name = Column(String(255), nullable=False)
    create_time = Column(DateTime, nullable=True)
    create_peo = Column(String(255), nullable=True)
    update_time = Column(DateTime, nullable=True)
    update_peo = Column(String(255), nullable=True)


class AreaDataBase(DataBase):

    def __init__(self):
        super(AreaDataBase, self).__init__()
        self.table = AreaItem.__tablename__

    @property
    def areas(self) -> dict:
        sql = f"SELECT `id`, `area_name` FROM `{self.table}`;"
        df = pd.read_sql(sql, self.engine)
        return df["area_name"].values.tolist()

    @property
    def areasDetails(self) -> list:
        sql = f"SELECT `id`, `area_name` FROM `{self.table}`;"
        df = pd.read_sql(sql, self.engine)
        result = []
        for tup in zip(df["id"], df["area_name"]):
            result.append({
                "id":   tup[0],
                "name": tup[1],
            })
        return result

    def query(self, areaId: int):
        sql = f"SELECT `area_name` FROM `{self.table}` WHERE `id`={areaId} LIMIT 0, 1;"
        df = pd.read_sql(sql, self.engine)
        result = df["area_name"]
        return None if len(result) == 0 else result[0]


if __name__ == "__main__":
    import json

    db = AreaDataBase()

    print(json.dumps(db.areas, ensure_ascii=False, separators=(", ", ": ")))
    """
    ["黄浦区", "徐汇区", "长宁区", "静安区", "普陀区", "虹口区", "杨浦区", "闵行区", "宝山区", "嘉定区", "浦东新区", "金山区", "松江区", "青浦区", "奉贤区", "崇明区"]
    """

    # print(json.dumps(db.areasDetails, ensure_ascii=False, separators=(", ", ": ")))
    """
    [{
        "id":   1,
        "name": "黄浦区"
    }, {
        "id":   2,
        "name": "徐汇区"
    }, {
        "id":   3,
        "name": "长宁区"
    }, {
        "id":   4,
        "name": "静安区"
    }, {
        "id":   5,
        "name": "普陀区"
    }, {
        "id":   6,
        "name": "虹口区"
    }, {
        "id":   7,
        "name": "杨浦区"
    }, {
        "id":   8,
        "name": "闵行区"
    }, {
        "id":   9,
        "name": "宝山区"
    }, {
        "id":   10,
        "name": "嘉定区"
    }, {
        "id":   11,
        "name": "浦东新区"
    }, {
        "id":   12,
        "name": "金山区"
    }, {
        "id":   13,
        "name": "松江区"
    }, {
        "id":   14,
        "name": "青浦区"
    }, {
        "id":   15,
        "name": "奉贤区"
    }, {
        "id":   16,
        "name": "崇明区"
    }]
    """

    # print(db.query(0))
    # print(db.query(11))
